import csv

data = []
cleaned_data = []

with open("baoshan-schools-2024.csv", mode="r", encoding="utf-8") as file:
    reader = csv.reader(file)
    for row in reader:
        if (row[0] != "") and (row[2] == "公办") and ("1:" in row[16]):
            ratio_parts = row[16].split(":")
            if len(ratio_parts) == 2:
                try:
                    ratio_value = int(ratio_parts[1])
                    staff_count = int(row[14])
                    estimated_students = staff_count * ratio_value
                    cleaned_row = [
                        row[0],  # serial_no
                        row[1],  # school_name
                        row[14],  # total_staff_count
                        row[15],  # full_time_teacher_count
                        str(estimated_students)  # estimated_student_count
                    ]
                    cleaned_data.append(cleaned_row)
                except (ValueError, IndexError):
                    continue


for row in cleaned_data:
    print(row)

# Save to CSV
with open("clean-baoshan-schools-2024.csv", mode="w", encoding="utf-8", newline='') as file:
    writer = csv.writer(file)
    writer.writerow([
        "serial_no",
        "school_name",
        "total_staff_count",
        "full_time_teacher_count",
        "estimated_student_count"
    ])
    writer.writerows(cleaned_data)

print("\nData saved to clean-baoshan-schools-2024.csv")